const oracledb = require('oracledb')

const xlsx = require('node-xlsx');
var fs = require('fs');

var moment = require('moment');

const DB_CONFIG = {
  user: 'cgxzcuser', 
  password: 'Tp3D1rBz#cx2024',
  //connectString: 'jdbc:oracle:thin:@172.50.100.181:1521:cgxzc' // 数据库地址：{IP:PORT/数据库名称}
  connectString: '172.50.100.181:1521/cgxzc' // 数据库地址：{IP:PORT/数据库名称}

  //  connectString: '172.50.100.169:1521/cgxzc',
  //         user: 'cgxzcuser_0522',
  //         password: 'cgxzcuser#2023'
}


console.log('运行模式:', oracledb.thin ? 'Thin' : 'Thick');

//oracledb.initOracleClient();
oracledb.initOracleClient({libDir: "C:\\Users\\15371\\scoop\\apps\\oracle-instant-client\\current"});

// oracledb.getConnection(DB_CONFIG).then( async connection => {
//   console.log('Connected successfully!');
//   // 执行 SQL 查询
//    result = await connection.execute(`SELECT * FROM AA_AARF`);
//       console.log('result:',result)
// }).catch(err => {
//   console.error('Connection error:', err);
// });

 const tableNames = [
    "AR_INVOICE",
    "AR_INVOICE_DTL",
    "AR_INVOICE_DTL_SO",
    "AR_YF",
    "AR_YF_DTL_SB",
    "BILL_MANAGEMENT",
    "CASH_INFO",
    "CF_ENQUIRY",
    "CNJ_BOM_STEP_SETTING",
    "CNJ_ORDER_BACK",
    "CNJ_PLINE_TIME_ARRANGE",
    "CNJ_PRO_LINE_RES_SETTINGS",
    "CNJ_PRO_LINE_RES_USE",
    "CONTRACT_MIDDLE",
    "CUSTOMER_COMPARISON",
    "EO_ORDER_MODIFY_FIELD",
    "EO_ORDER_MODIFY_RECORD",
    "IL_ACCEPT_COMPANY",
    "IL_COPRODUCT_FREIGHT",
    "IL_COPRODUCT_FREIGHT_DTL",
    "IL_COPRODUCT_WEIGHT_STOCK_Q",
    "IL_COPRODUCT_WEIGHT_STOCK_T",
    "IL_CRANE_MSG",
    "IL_FREIGHT",
    "IL_FREIGHT_DTL",
    "IL_FREIGHT_SETTLE",
    "IL_FREIGHT_STOCK",
    "IL_FREIGHT_STOCK_MOVE",
    "IL_FREIGHT_ZBS",
    "IL_INVOICE_APPLY",
    "IL_SHIPPING_FREIGHT",
    "IL_STORE_PERSON",
    "IL_VEHICLE",
    "IL_WEIGHT_ACQUISITION_T",
    "IL_WEIGHT_PREDICT_Q",
    "LIMIT_DEFAULT_NEW",
    "MS_DEPT_MANAGEMENT",
    "PRODUCT_MAIN_CLASS",
    "SALE_DEPT",
    "SALE_POST",
    "SALE_SEQ_INC",
    "SALE_USER_INFO",
    "SA_ACCOUNTS_RECEIVABLE_FINANCE",
    "SA_ALLOCATION_ORDER",
    "SA_ALLOCATION_YF",
    "SA_ALLOCATION_ORDER_TRANS",
    "SA_INVOICE_ALLOCATE_AMT",
    "SA_LOCK_AMT",
    "SA_RECEIPT",
    "SA_RECEIPT_AUTH",
    "SA_RECPIT_SALESUNIT",
    "SA_RELEASE_RECORD",
    "SA_SALESUNIT_REMAINING",
    "SB_BACK_GOOD",
    "SB_BACK_GOOD_MAIN",
    "SB_BELONG",
    "SB_BILL",
    "SB_BILL_DTL",
    "SB_BILL_OUTSOURCE",
    "SB_BILL_OUTSOURCE_DTL",
    "SB_BILL_STOCK",
    "SB_BILL_STOCK_MOVE",
    "SB_BILL_STOCK_SEND",
    "SB_CHANGE_PRICE",
    "SB_CHANGE_PRICE_DTL",
    "SB_COPRODUCT_BILL",
    "SB_COPRODUCT_BILL_DTL",
    "SB_CUSTOMER_STORE_REL",
    "SB_ED_ZBS_BILL",
    "SB_ED_ZBS_BILL_DTL",
    "SB_ED_ZBS_BILL_STOCK",
    "SB_ED_ZBS_INVOICE",
    "SB_ENTRUST",
    "SB_EXPORT_RETURN",
    "SB_EXTEND_PROCESS",
    "SB_EXTEND_PROCESS_DTL",
    "SB_JUDGEFAIL_CONFIRM",
    "SB_MATERIAL_FLOW_STORE_TYPE",
    "SB_MAT_INFO_DETAIL",
    "SB_MAT_INFO_DETAIL_HIS",
    "SB_MAT_INFO_DETAIL_TRAN",
    "SB_MAT_INFO_HOLD_HIS",
    "SB_MAT_INFO_UNPACK",
    "SB_MAT_REVERSE",
    "SB_MAT_REVERSE_ITEM",
    "SB_MAT_REVERSE_MAIN",
    "SB_MAT_SETTLE_SPLIT_LOG",
    "SB_MAT_SPLIT_OLD_BILL",
    "SB_MONTHLY_VEHICLE_APPLICATION",
    "SB_MONTHLY_VEHICLE_APPROVAL",
    "SB_OTHER_ENTRUST",
    "SB_PAY_INTO_RETURN",
    "SB_PRIVATE_LINE",
    "SB_QUANTITY_DIFF",
    "SB_QUANTITY_DIFF_ITEM",
    "SB_RAIL_DAY_ITEM",
    "SB_RAIL_DAY_MAIN",
    "SB_RAIL_MONTH",
    "SB_SENDPLAN_ITEM",
    "SB_SENDPLAN_MAIN",
    "SB_STATION",
    "SB_STOCK_HIS",
    "SB_STORE",
    "SB_STORE_HIS",
    "SB_TEMP_OLD_SYS",
    "SF_LIMITUSE_DTL_NEW",
    "SF_LIMIT_NEW",
    "SO_ADMIN_DISTRICT",
    "SO_COPRODUCT_SPEC",
    "SO_CUSTNO_DELIVERY",
    //"SO_CUSTOMER_CLASSIFICATION",
    "SO_CUSTOMLINE",
    "SO_DEL_MATERIAL",
    "SO_DEL_MATERIAL_REQUIRE",
    "SO_DISTRICT",
    "SO_FUND",
    "SO_ORDER",
    "SO_ORDER_ADDITIONAL_REQUIRE",
    "SO_ORDER_CHANGE_RECORD",
    "SO_ORDER_DTL",
    "SO_ORDER_DTL_SYNC_PRICE_TEMP",
    "SO_ORDER_FIELD_COMPARE",
    "SO_ORDER_FORCE_EFFECT",
    "SO_ORDER_FORCE_EFFECT_DTL",
    "SO_ORDER_OLD",
    "SO_ORDER_OLE_FINAL",
    "SO_ORDER_QUALITY",
    "SO_ORDER_QUALITY_INVOICE",
    "SO_ORDER_REQUIRE_EXTRA",
    "SO_OUTSOURCE_SPEC",
    "SO_OUTSOURCE_STD",
    "SO_PAYMENT_NODE_INFO",
    "SO_PLINE_SUPERVISE_SEND_USER",
    "SO_PRE_TEC_REVIEW",
    "SO_PRE_TEC_REVIEW_DTL",
    "SO_PRODUCT_ORDER_CLOSE_APPLY",
    "SO_SALE_ORG_MAPPING",
    "SO_SEARCH",
    "SO_SEARCH_DTL",
    "SO_SEARCH_REQUIRE_EXTRA",
    "SO_SEARCH_REQUIRE_EXTRA_DTL",
    "SO_SEARCH_TEMPLATE",
    "SO_SEC_PROD_CONT",
    "SO_SEC_PROD_CONT_DTL",
    "SO_SUPERVISE_COMPANY",
    "SO_SUPERVISE_PERSON",
    "SO_SUPERVISE_RELATION",
    "SO_TECH_REVIEWER_REL",
    "SR_PRICE",
    "SS_CHECK_DTL",
    "SS_CHECK_DTL_SUM",
    "SS_CHECK_DTL_SUM_SPLIT",
    "SS_CHECK_MAIN",
    "SS_HM_INVOICE",
    "SS_HM_INVOICE_DTL",
    "SS_INVOICE",
    "SS_INVOICE_DTL",
    "SS_INVOICE_AUTH",
    "SS_JT",
    "SS_JTGM_INVOICE",
    "SS_JTGM_INVOICE_DTL",
    "SS_JTGM_INVOICE_DTL_MAT",
    "SS_RELATED_KJ_ORDER",
    "SS_REL_PRICE",
    "SS_SALES_ADJUST",
    "SS_TAX_NO",
    "SS_UNCHECK_DTL",
    "SS_UNCHECK_SPLIT_LOG",
    "SB_STOCK_BALANCE",
    "CM_LOG_BUSINESS_TYPE",
    "CNJ_OP_INF_HIS",
    "EO_ORDER_MODIFY_FUND",
    "EO_ORDER_MODIFY_REQUIRE",
    "SALE_DISTRICT",
    "SB_MES_ERROR_LOG",
    "SB_OTHER_ENTRUST_DTL",
    "SB_SYNC_MES_INFO",
    "SO_CHANGE_MONTH",
    "SO_CUSTNO_RECEIVE",
    "SO_ORDER_DTL_LOG",
    "SO_REVIEW_NOTE",
    "SO_TEMP_CUSTOMER",
    "SALE_BUSINESS_INVOKE_LOG"
];

//''
//@TGCXDB_88
//@CGXZCDB_ZSJ
const getSql = (dblink)=>{
  //CGXZCUSER
  let schema  = dblink==''?'CGXZCUSER.':''
    const sqlCount = tableNames.map(table=>`(select count(1)  from ${schema}${table}${dblink}) as ${table}`).reduce((a,b)=>a+','+b)
    return `select ${sqlCount} from dual`
}

const dbResult = {
  '180':{
    dblink:'',
    result:[]
  },
  '88':{
    dblink:'@TGCXDB_88',
    result:[]
  },
  'zsj':{
    dblink:'@CGXZCDB_ZSJ',
    result:[]
  }
}


// 使用连接池
async function run() {
  let pool;
  try {
    pool = await oracledb.createPool(DB_CONFIG);
    let connection;
    try {
      connection = await pool.getConnection();

      for(var it in dbResult){
        console.log('it:',it)
        const item = dbResult[it]
        const sqls = getSql(item.dblink);
        console.log('sqls:',sqls)
      //     const result = await  connection.execute(sqls)
      //     // for(var t of tableNames){
      //     //   console.log('t:',t)
      //     //       const result = await connection.execute(`select count(1)  from ${t}@CGXZCDB_ZSJ`)
                
      //     //       console.log('result:',result)
      //     // }
         
      //     // for(var ii in result.metaData){
      //     //   const o = {}
      //     //   o[result.metaData[ii].name] = result.rows[0][ii]
            
      //     // }
      //     // console.log('result:',result)
      //      item.result=result
       }
      //  const I180 = dbResult['180'].result
      //  const I88 = dbResult['88'].result
      //   const IZSJ = dbResult['zsj'].result
      //     var buffer = xlsx.build([
      //            {name:'统计',data:[
      //             ['180'],
      //             I180.metaData.map(s=>s.name),...I180.rows,
      //              ['88'],
      //              I88.metaData.map(s=>s.name),...I88.rows,
      //               ['ZSJ'],
      //              IZSJ.metaData.map(s=>s.name),...IZSJ.rows,
      //            ]},
      //       ]);
      //        fs.writeFileSync(`./统计.xlsx`,buffer,{'flag':'w'});

   //  console.log('dbResult:',JSON.stringify(dbResult))
     // console.log('sql:',sql)
     
     // const 新产销与老产销Result = await 
    //  for(var table of tableNames){
    //     var sd = await connection.execute(`select count(1) as ${table}Count from ${table}`)
    //     console.log('sd:',sd)
    //  }
   
     
  
    } catch (err) {
      throw (err);
    } finally {
      if (connection) {
        try {
          await connection.close(); 
        } catch (err) {
          throw (err);
        }
      }
    }
  } catch (err) {
    console.log(err)
    throw err;
  } finally {
    await pool.close();
  }
}
run()